package com.eam.handler;

import com.basis.utils.BaseServletHandler;
import com.eam.data.EamSpareparts;
import com.eam.data.EamSparepartsInventory;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.codehaus.jettison.json.JSONObject;
import org.openbravo.dal.service.OBDal;
import org.openbravo.database.SessionInfo;
import org.openbravo.model.ad.access.User;
import org.openbravo.model.common.enterprise.Locator;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/*
 * 设备备品配件盘点导入
 */

public class SparepartsInventoryImportHandler  extends BaseServletHandler {
	
	private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");//处理时间格式

	@Override
	protected JSONObject service(HttpServletRequest request, HttpServletResponse response) {
		JSONObject result = new JSONObject();
		try {
			result.put("desc", "系统繁忙，请稍后再试。");
			result.put("result", false);
			request.setCharacterEncoding("utf-8");
			DiskFileItemFactory factory = new DiskFileItemFactory();
			// 设置 缓存的大小，当上传文件的容量超过该缓存时，直接放到 暂时存储室
			factory.setSizeThreshold(1024 * 1024);
			ServletFileUpload upload = new ServletFileUpload(factory);
			// 可以上传多个文件
			List<FileItem> listItem = (List<FileItem>) upload.parseRequest(request);
			for (FileItem item : listItem) {
				if (!item.isFormField()) {
					String name = item.getName();
					if (item.getSize() > 3 * 1024 * 1024) {
						result.put("desc", "文件大小不能超过3M");
						return result;
					}
					InputStream is = item.getInputStream(); // 文件流
					try {
						Workbook wb = null;
						if (name.endsWith(".xls")) {
							wb = new HSSFWorkbook(is);
						} else if (name.endsWith(".xlsx")) {
							wb = new XSSFWorkbook(is);
						}
						if (wb != null) {
							result = this.readExcel(wb);
							if (!result.getBoolean("result")) {
								OBDal.getInstance().rollbackAndClose();
								return result;
							} else {
								OBDal.getInstance().commitAndClose();
								result.put("result", true);
								result.put("desc", "导入成功");
								return result;
							}
						} else {
							result.put("desc", "文件格式错误，至于许EXCEL的导入");
							return result;
						}
					} catch (Exception e) {
						OBDal.getInstance().rollbackAndClose();
						if (e.getClass().getName().contains("ConstraintViolationException")) {
							result.put("result", false);
							result.put("desc", "文件数据有问题，请仔细检查。");
						}
						e.printStackTrace();
					} finally {
						is.close();
					}
				}
			}
		} catch (Exception e) {
			OBDal.getInstance().rollbackAndClose();
			e.printStackTrace();
		}
		return result;
	}
	
	//2
	/**
	 * 读取excel记录
	 * 
	 * @param wb
	 * @return
	 * @throws Exception
	 */
	private JSONObject readExcel(Workbook wb) throws Exception {
		String meg = "", errorMsg;
		int number = wb.getNumberOfSheets();
		JSONObject result = new JSONObject();
		result.put("result", true);
		if (number <= 0) {
			result.put("result", false);
			result.put("desc", "文件格式错误。请仔细检查");
			return result;
		} else {
			// 读取第一页
			Sheet sheet = wb.getSheetAt(0);
			int lastRowNum = sheet.getLastRowNum();
			for (int i = 1; i <= lastRowNum; i++) {
				Row row = sheet.getRow(i);
				errorMsg = this.checkData(row);
				if (errorMsg != null && !"".equals(errorMsg)) {
					meg = errorMsg;
				}
				
			}
			if (!"".equals(meg)) {
				result.put("result", false);
				result.put("desc", meg);
			}
			return result;
			
		}
	}
	
	
	/**
	 * 开始存储记录
	 * @param row
	 * @return
	 * @throws Exception
	 */
	
	private String checkData(Row row) throws Exception {
			 
		String meg = "";
		String code = readValue(row.getCell(1));  //物料编码
		String planName = readValue(row.getCell(2)); //物料名称
		String Organizations = readValue(row.getCell(3)); //规格/型号
		String locators = readValue(row.getCell(4)); //储位
		String pandianqtys = readValue(row.getCell(5)); //盘点数量
		
		
		//查询该储位对应的物料信息
		String sql = "select * from Eam_Spareparts where code = ? ";
		
		EamSpareparts materil = (EamSpareparts)OBDal.getInstance().getSession().createSQLQuery(sql).addEntity(EamSpareparts.ENTITY_NAME).setString(0, code).uniqueResult();
		
		//查询当前库位的ID
		String  wql = "select * from m_locator where barcode = ? ";
		
		Locator KUWEI = (Locator)OBDal.getInstance().getSession().createSQLQuery(wql).addEntity(Locator.ENTITY_NAME).setString(0, locators).uniqueResult();
		
		//查询当家登录账户的ID
		String userIdtime = SessionInfo.getUserId(); //登录人的ad_user_id
		
		String eql = "select * from ad_user where ad_user_id = ?";
		
		User userIds = (User)OBDal.getInstance().getSession().createSQLQuery(eql).addEntity(User.ENTITY_NAME).setString(0, userIdtime).uniqueResult();
		
		//查询当前库位的库存数量
		String aql = "select ((select sum(jl.numbers)   "
				+ "from Eam_Spareparts_Collar jl  "
				+ " left join Eam_Spareparts  wl on jl.Eam_Spareparts_id = wl.Eam_Spareparts_id   "
				+ " left join m_locator cw on cw.m_locator_id = jl.m_locator_id   "
				+ "  where jl.type = 'added' and wl.code = '"+code+"'  and cw.barcode = '"+locators+"') - "
				+ " (select NVL(sum(jl.numbers), 0)  from Eam_Spareparts_Collar jl "
				+ " left join Eam_Spareparts  wl on jl.Eam_Spareparts_id = wl.Eam_Spareparts_id  "
				+ " left join m_locator cw on cw.m_locator_id = jl.m_locator_id "
				+ " where jl.type = 'Out' and wl.code = '"+code+"'  and cw.barcode = '"+locators+"' and jl.status != 'Notthrough')) as qty  from dual ";
		
		Long sumqty = (Long)OBDal.getInstance().getSession().createSQLQuery(aql).uniqueResult();
		
		int sumqtys = Integer.parseInt(String.valueOf(sumqty)); 
		 int qty = Integer.parseInt(pandianqtys);
		
		BigDecimal qtys = new BigDecimal(qty);
		
		Date time  =sdf.parse(sdf.format(new Date()));  //获取当前系统时间
		
		String status = "";
		
		EamSparepartsInventory INVENTORY = new EamSparepartsInventory();
		
		if(sumqtys > qty){
			status = "loss";
		}else if(sumqtys < qty){
			status = "Profit";
		}else{
			status = "normal";
		}
	    
		INVENTORY.setEamSpareparts(materil);
		INVENTORY.setLocator(KUWEI);
		INVENTORY.setStatus(status);
		INVENTORY.setCompany(materil.getCompany());
		INVENTORY.setNumbers(qtys);
		INVENTORY.setUser(userIds);
		INVENTORY.setInventoryTime(time);
		OBDal.getInstance().save(INVENTORY);
		
		return meg;
	}
		
	//}
	//3
	// CELL_TYPE_NUMERIC 数值型 0
	// CELL_TYPE_STRING 字符串型 1
	// CELL_TYPE_FORMULA 公式型 2
	// CELL_TYPE_BLANK 空值 3
	// CELL_TYPE_BOOLEAN 布尔型 4
	// CELL_TYPE_ERROR 错误 5
	private String readValue(Cell cell) throws Exception {
		if (cell == null) {				return "";
		}
		if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
			if (DateUtil.isCellDateFormatted(cell)) {
				return sdf.format(cell.getDateCellValue());
			}
			Double text = cell.getNumericCellValue();
			return String.valueOf(text.longValue());
		} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
			return cell.getStringCellValue();
		} else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
			throw new Exception("第" + (cell.getRowIndex() + 1) + "行，" + (cell.getColumnIndex() + 1) + "列，数据格式有问题,请检查");
		} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(cell.getBooleanCellValue());
		} else {
			return "";
		}
	}
}